IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_DistributionInspectionTypesByWeekAndInspector')
	BEGIN
		DROP  Procedure  up_Report_DistributionInspectionTypesByWeekAndInspector
	END

GO

CREATE Procedure up_Report_DistributionInspectionTypesByWeekAndInspector
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SELECT  @ProjectId = CONVERT(INT, SettingValue) FROM tb_Settings
	WHERE SettingKey like 'DistributionInspectionProjectId'
END

SELECT
	C.Name as Inspector_Name,
	T.Name as InspectionType_Name,
	IR.Vendor_Distribution_TypeFk as InspectionType,
	P.InspectorFk as Inspector,
	1 as numberInspection,
	convert(nvarchar,dbo.fn_GetFirstDateofWeek(P.AcquisitionDate),101) as FirstWeekDate,
	convert(nvarchar,dbo.fn_GetLastDateofWeek(P.AcquisitionDate),101) as LastWeekDate,
	DATEPART(wk, P.AcquisitionDate) AS weekNumber,
	P.PoleTag,
	P.GroundWireRepair, 
	P.GuardsQuantity, 
	P.Billable
	,(
		SELECT COUNT(*) FROM tb_Vendor_Distribution_Transformers 
		WHERE Vendor_Distribution_PoleFk = P.Vendor_Distribution_Poles_PoleId	
	) Transformer
	,(
		SELECT COUNT(*) FROM tb_Vendor_Distribution_Sectionalizers
		WHERE Vendor_Distribution_PoleFk = P.Vendor_Distribution_Poles_PoleId	
	) Sectionalizer
	,(
		SELECT COUNT(*) FROM tb_Vendor_Distribution_Reclosers
		WHERE Vendor_Distribution_PoleFk = P.Vendor_Distribution_Poles_PoleId	
	) Recloser
FROM tb_Vendor_Distribution_Poles P
INNER JOIN tb_Vendor_Contacts C 
	ON P.InspectorFk = C.Vendor_ContactId
INNER JOIN tb_Vendor_Distribution_InspectionResults IR 
	ON IR.Vendor_Distribution_PoleFk = P.Vendor_Distribution_Poles_PoleId
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type T 
	ON IR.Vendor_Distribution_TypeFk = T.Vendor_InspVisit_XmissionStructures_Inspection_TypeId
WHERE 
	P.DistributionType = 1 -- Inspection
	AND P.ProjectFk = @ProjectId
ORDER BY 
	DATEPART(wk, P.AcquisitionDate)

